Excel BI - Excel Challenge 672

excel-challenges

excel-formulas

🔰 Level 1 is sum of Level2.

Published

March 24, 2026

Illustration for Excel BI - Excel Challenge 672

Challenge Description

🔰 Level 1 is sum of Level2. Work out the values marked as X.

Solutions

library(tidyverse)
library(readxl)

path = "Excel/672 Find Level Entries.xlsx"
input = read_excel(path, range = "A3:B10", col_names = c("Level1", "Level2"))
test  = read_excel(path, range = "D3:E10", col_names = c("Level1", "Level2"))

result = input %>%
  mutate(rn = row_number(), Level = Level1) %>% 
  fill(Level1) %>%
  arrange(Level1, Level2) %>%
  mutate(across(everything(), as.numeric),
         Level2 = ifelse(is.na(Level2), Level1 - lag(Level2), Level2)) %>%
  arrange(rn) %>%
  select(Level1 = Level, Level2)

all.equal(result, test, check.attributes = FALSE) # True

Logic: Read the workbook ranges needed for the challenge; Derive the required intermediate columns; Apply the business rule conditions explicitly.
Strengths: The code maps the workbook rule into a compact, reproducible pipeline.
Areas for Improvement: The solution assumes the workbook layout and selected ranges remain stable, so any structural change in the sheet would require small adjustments.
Gem: The elegant part is how little code is needed once the correct intermediate representation is chosen.

import pandas as pd
import numpy as np

path = "672 Find Level Entries.xlsx"
input = pd.read_excel(path, usecols="A:B", skiprows=1, nrows=8, names=["Level1", "Level2"])
test = pd.read_excel(path, usecols="D:E", skiprows=1, nrows=8, names=["Level1", "Level2"])
test['Level2'] = test['Level2'].astype(float)

input['rn'] = range(1, len(input) + 1)
input['Level'] = input['Level1']
input['Level1'] = input['Level1'].ffill()
input = input.sort_values(by=['Level1', 'Level2'])
input = input.apply(pd.to_numeric, errors='coerce')
input["Level2"] = np.where(
    input["Level2"].isna(),
    input["Level1"] - input["Level2"].shift(1),
    input["Level2"]
)
result = input.sort_values(by=['rn'])
result = result[["Level", "Level2"]].rename(columns={"Level": "Level1"})

print(test.equals(result)) # True

The Python version mirrors the same workbook logic with a concise, direct implementation.

Difficulty Level

Easy / Medium

The business rule is clear, though the workbook still needs a few transformation steps to reach the expected output.